﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using CardLogistics.Model.PutInCard;

namespace CardLogistics.DAL.PutInCard
{
    /// <summary>
    /// 半成品/成品卡入库发卡文件_数据访问类
    /// </summary>
    public class PutInBadCardData
    {
        #region 查询
        /// <summary>
        /// 根据条件查询相应的表的字段
        /// </summary>
        /// <param name="StrWhere">查询条件</param>
        /// <param name="StrFields">要查询的字段</param>
        /// <returns>填充完数据的DataTable</returns>
        public DataTable GetList(string StrWhere, string StrFields)
        {
            return CommonData.GetListOrderBy(StrWhere, "v_PutInBadCard", StrFields, "ID", 0);
        }

        /// <summary>
        /// 根据编号查询基本信息
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns>填充完数据的DataTable</returns>
        public DataTable GetInfoById(string id)
        {
            return GetList(" ID = '" + id + "' ", "*");
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public PutInBadCard GetModel(string rId)
        {
            PutInBadCard model = new PutInBadCard();
            DataRow row = GetInfoById(rId).Rows[0];
            if (row != null)
            {
                if (row["ID"] != null)
                {
                    model.ID = row["ID"].ToString();
                }
                if (row["BADCARDNO"] != null)
                {
                    model.BADCARDNO = row["BADCARDNO"].ToString();
                }
                if (row["BADCARDDATE"] != null && row["BADCARDDATE"].ToString() != "")
                {
                    model.BADCARDDATE = DateTime.Parse(row["BADCARDDATE"].ToString());
                }
                if (row["OUTSTORAGEID"] != null)
                {
                    model.OUTSTORAGEID = row["OUTSTORAGEID"].ToString();
                }
                if (row["REMARKS"] != null)
                {
                    model.REMARKS = row["REMARKS"].ToString();
                }
                if (row["LASTUPDATEUSER"] != null)
                {
                    model.LASTUPDATEUSER = row["LASTUPDATEUSER"].ToString();
                }
                if (row["LASTUPDATEDATE"] != null && row["LASTUPDATEDATE"].ToString() != "")
                {
                    model.LASTUPDATEDATE = DateTime.Parse(row["LASTUPDATEDATE"].ToString());
                }
                if (row["ADDTIME"] != null && row["ADDTIME"].ToString() != "")
                {
                    model.ADDTIME = DateTime.Parse(row["ADDTIME"].ToString());
                }

                if (row["OutStorageNo"] != null)
                {
                    model.OutStorageNo = row["OutStorageNo"].ToString();
                }
            }
            return model;
        }
        #endregion

        #region 新增
        /// <summary>
        /// 新增信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Add(PutInBadCard model)
        {

            try
            {
                OracleParameter[] parameters = {
                    new OracleParameter("varOut", OracleType.VarChar,50),
					new OracleParameter("vBADCARDNO", OracleType.NVarChar),
					new OracleParameter("vBADCARDDATE", OracleType.DateTime),
					new OracleParameter("vOUTSTORAGEID", OracleType.NVarChar),
					new OracleParameter("vREMARKS", OracleType.NVarChar),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
					new OracleParameter("vADDTIME", OracleType.DateTime)};
                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = model.BADCARDNO;
                parameters[2].Value = model.BADCARDDATE;
                parameters[3].Value = model.OUTSTORAGEID;
                parameters[4].Value = model.REMARKS;
                parameters[5].Value = model.LASTUPDATEUSER;
                parameters[6].Value = model.LASTUPDATEDATE;
                parameters[7].Value = model.ADDTIME;

                string StrOutPar = DbHelperSQL.RunProcedure("P_PutInBadCard_Add", parameters, parameters[0]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                return "err," + ex.Message;
            }
        }
        #endregion

        #region 重载新增信息
        /// <summary>
        /// 重载新增信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Add(PutInBadCard model, string FileValue)
        {
                string StrOutPar = string.Empty;//返回值
            string[] split = FileValue.Split(';');//以分号分隔
            int count = split.Length; //计算行数
            int result = 0; //受影响的行数
            int val = 0;
            string sqlConn = DbHelperSQL.ConnectionString;
            using (OracleConnection conn = new OracleConnection(sqlConn))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
            try
            {
                OracleParameter[] parameters = {
                    new OracleParameter("varOut", OracleType.VarChar,50),
					new OracleParameter("vBADCARDNO", OracleType.NVarChar),
					new OracleParameter("vBADCARDDATE", OracleType.DateTime),
					new OracleParameter("vOUTSTORAGEID", OracleType.NVarChar),
					new OracleParameter("vREMARKS", OracleType.NVarChar),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
					new OracleParameter("vADDTIME", OracleType.DateTime)};
                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = model.BADCARDNO;
                parameters[2].Value = model.BADCARDDATE;
                parameters[3].Value = model.OUTSTORAGEID;
                parameters[4].Value = model.REMARKS;
                parameters[5].Value = model.LASTUPDATEUSER;
                parameters[6].Value = model.LASTUPDATEDATE;
                parameters[7].Value = model.ADDTIME;

                 StrOutPar = DbHelperSQL.RunProcedure("P_PutInBadCard_Add", parameters, parameters[0]).ToString();
                //插入明细信息
                if (StrOutPar.Split(',')[0] == "ok")
                {
                    string badCardId = StrOutPar.Split(',')[1]; //得到主键Id
                    StringBuilder strSql = new StringBuilder();

                    strSql.Append(" delete from putinbadcard_detail where badCardId ='" + badCardId + "'");
                    result = CardLogistics.DAL.DbHelperSQL.ExecuteSql(strSql.ToString());
                    for (int i = 0; i < count; i++)
                    {
                        strSql = new StringBuilder();//初始化
                        strSql.Append("insert into putinbadcard_detail");
                        strSql.Append("(badCardId,actualnumber,startcardno,endcardno)");
                        strSql.Append(" values(");
                        strSql.Append("'" + badCardId + "',");
                        strSql.Append("" + split[i] + "");
                        strSql.Append(")");
                        if (!String.IsNullOrEmpty(strSql.ToString()))
                        {
                            cmd.CommandText = strSql.ToString();
                            val += cmd.ExecuteNonQuery();
                            #region 插入卡明细
                            DbHelperSQL.CardRecord(Int32.Parse(split[i].Split('|')[1]), Int32.Parse(split[i].Split('|')[2]), "0", badCardId, 3, model.BADCARDDATE, i);
                            #endregion
                        }
                    }
                }
                tx.Commit();
            }
            catch (System.Data.OracleClient.OracleException ex)
            {
                tx.Rollback();
                throw new Exception(ex.Message);
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
            }
            return StrOutPar;
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Update(PutInBadCard model)
        {
            try
            {
                OracleParameter[] parameters = {
				    new OracleParameter("vID", OracleType.VarChar,50),
					new OracleParameter("vBADCARDNO", OracleType.NVarChar),
					new OracleParameter("vBADCARDDATE", OracleType.DateTime),
					new OracleParameter("vOUTSTORAGEID", OracleType.NVarChar),
					new OracleParameter("vREMARKS", OracleType.NVarChar),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
					new OracleParameter("vADDTIME", OracleType.DateTime),
                    new OracleParameter("varOut", OracleType.VarChar,50)};
                parameters[0].Value = model.ID;
                parameters[1].Value = model.BADCARDNO;
                parameters[2].Value = model.BADCARDDATE;
                parameters[3].Value = model.OUTSTORAGEID;
                parameters[4].Value = model.REMARKS;
                parameters[5].Value = model.LASTUPDATEUSER;
                parameters[6].Value = model.LASTUPDATEDATE;
                parameters[7].Value = model.ADDTIME;
                parameters[8].Direction = ParameterDirection.Output;
                string StrOutPar = DbHelperSQL.RunProcedure("P_PutInBadCard_Update", parameters, parameters[8]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                return "err," + ex.Message;
            }
        }
        #endregion



        #region 重载修改信息
        /// <summary>
        /// 重载修改信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Update(PutInBadCard model, string FileValue)
        {
            string StrOutPar = string.Empty;//返回值
            string[] split = FileValue.Split(';');//以分号分隔
            int count = split.Length; //计算行数
            int result = 0; //受影响的行数
            int val = 0;
            string sqlConn = DbHelperSQL.ConnectionString;
            using (OracleConnection conn = new OracleConnection(sqlConn))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    OracleParameter[] parameters = {
				    new OracleParameter("vID", OracleType.VarChar,50),
					new OracleParameter("vBADCARDNO", OracleType.NVarChar),
					new OracleParameter("vBADCARDDATE", OracleType.DateTime),
					new OracleParameter("vOUTSTORAGEID", OracleType.NVarChar),
					new OracleParameter("vREMARKS", OracleType.NVarChar),
					new OracleParameter("vLASTUPDATEUSER", OracleType.VarChar,50),
					new OracleParameter("vLASTUPDATEDATE", OracleType.DateTime),
					new OracleParameter("vADDTIME", OracleType.DateTime),
                    new OracleParameter("varOut", OracleType.VarChar,50)};
                    parameters[0].Value = model.ID;
                    parameters[1].Value = model.BADCARDNO;
                    parameters[2].Value = model.BADCARDDATE;
                    parameters[3].Value = model.OUTSTORAGEID;
                    parameters[4].Value = model.REMARKS;
                    parameters[5].Value = model.LASTUPDATEUSER;
                    parameters[6].Value = model.LASTUPDATEDATE;
                    parameters[7].Value = model.ADDTIME;
                    parameters[8].Direction = ParameterDirection.Output;
                    StrOutPar = DbHelperSQL.RunProcedure("P_PutInBadCard_Update", parameters, parameters[8]).ToString();
                    //插入明细信息
                    if (StrOutPar.Split(',')[0] == "ok")
                    {
                        string badCardId = StrOutPar.Split(',')[1]; //得到主键Id
                        StringBuilder strSql = new StringBuilder();

                        strSql.Append(" delete from putinbadcard_detail where badCardId ='" + badCardId + "'");
                        result = CardLogistics.DAL.DbHelperSQL.ExecuteSql(strSql.ToString());
                        for (int i = 0; i < count; i++)
                        {
                            strSql = new StringBuilder();//初始化
                            strSql.Append("insert into putinbadcard_detail");
                            strSql.Append("(badCardId,actualnumber,startcardno,endcardno)");
                            strSql.Append(" values(");
                            strSql.Append("'" + badCardId + "',");
                            strSql.Append("" + split[i] + "");
                            strSql.Append(")");
                            if (!String.IsNullOrEmpty(strSql.ToString()))
                            {
                                cmd.CommandText = strSql.ToString();
                                val += cmd.ExecuteNonQuery();
                                #region 插入卡明细
                                DbHelperSQL.CardRecord(Int32.Parse(split[i].Split('|')[1]), Int32.Parse(split[i].Split('|')[2]), "0", badCardId, 3, model.BADCARDDATE, i);
                                #endregion
                            }
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.OracleClient.OracleException ex)
                {
                    tx.Rollback();
                    throw new Exception(ex.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
            return StrOutPar;
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="id">删除信息的ID</param>
        /// <returns>删除成功返回True。否则返回false</returns>
        public string Delete(string id)
        {
            try
            {
                OracleParameter[] parameters = {
                        new OracleParameter("varOut",  OracleType.VarChar,100),
					    new OracleParameter("vid",  OracleType.VarChar,50)
                };
                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = id;
                string StrOutPar = DbHelperSQL.RunProcedure("P_PutInBadCard_Delete", parameters, parameters[0]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}
